package com.lwj.project1.databaseHelper;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import androidx.annotation.Nullable;

import com.lwj.project1.entity.Game;
import com.lwj.project1.entity.GameCategory;
import com.lwj.project1.entity.Model;
import com.lwj.project1.entity.Post;
import com.lwj.project1.entity.User;

import java.util.ArrayList;
import java.util.List;

public class MySqliteOpenHelper extends SQLiteOpenHelper {
    private final String createUserTableSql = "create table user(" +
            "user_id INTEGER PRIMARY KEY AUTOINCREMENT ," +
            "user_name varchar(50) unique not null ," +
            "user_phone varchar(20)," +
            "user_email varchar(50) unique not null ," +
            "user_password varchar(20) not null ," +
            "user_gender varchar(10) default '秘' ," +
            "user_description varchar(255) ," +
            "user_head_img blob " +
            ");";
    private final String createGameCategoryTableSql = "create table if not exists game_category("+
            "game_category_id INTEGER PRIMARY KEY AUTOINCREMENT ," +
            "game_category_name varchar(255) unique not null ," +
            "game_category_description varchar(255) not null ," +
            "game_category_model varchar(20) not null ,"+
            "game_category_img blob " +
            ");";
    private final String createGameTableSql = "create table if not exists game(" +
            "game_id INTEGER PRIMARY KEY AUTOINCREMENT ," +
            "game_description varchar(255) not null ," +
            "owner_name int not null ," +
            "game_category_id int not null ," +
            "foreign key (owner_name) references user(user_name)," +
            "foreign key (game_category_id) references game_category(game_category_id)" +
            ");";
    private final String createLikeTableSql = "create table if not exists ilike(" +
            "user_name varchar(20) not null ," +
            "game_id int not null ," +
            "foreign key (user_name) references user(user_name) ," +
            "foreign key (game_id) references game(game_id) ," +
            "primary key (user_name,game_id));";
    private final String createPostAndUserTableSql = "";


    public MySqliteOpenHelper(@Nullable Context context) {
        super(context,"project1.db",null,1);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(createUserTableSql);
        db.execSQL(createGameCategoryTableSql);
        db.execSQL(createGameTableSql);
        db.execSQL(createLikeTableSql);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    // 用户增改查
    public Boolean addUser(User user){
        SQLiteDatabase db = getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("user_name",user.getUserName());
        values.put("user_password",user.getPassword());
        values.put("user_head_img",user.getHeadImgStr());
        values.put("user_phone",user.getPhone());
        values.put("user_email",user.getEmail());
        values.put("user_gender",user.getGender());
        values.put("user_description",user.getDescription());
        if (db.insert("user",null,values) != -1) return Boolean.TRUE;
        return Boolean.FALSE;
    }
    public Boolean updateUser(User user){
        if (user == null) return Boolean.FALSE;
        SQLiteDatabase db = getWritableDatabase();
        ContentValues values = new ContentValues();
        if (user.getHeadImgStr() != null) {
            values.put("user_head_img",user.getHeadImgStr());
        }
        values.put("user_phone",user.getPhone());
//        Log.i("addPhone",user.getPhone());
        values.put("user_email",user.getEmail());
        values.put("user_gender",user.getGender());
        values.put("user_description",user.getDescription());
        if (db.update("user",values,"user_name like ?",new String[]{user.getUserName()}) > 0){
            return Boolean.TRUE;
        }
        return Boolean.FALSE;
    }
    public User findUserByName(String username){
        SQLiteDatabase db = getReadableDatabase();
        System.out.println("username is " + username);
        Cursor cursor = db.query("user",new String[]{"user_id","user_name","user_password","user_phone","user_head_img","user_email","user_gender","user_description"},"user_name like ?",new String[]{username},null,null,null);
        if (cursor == null) return null;
        User user = null;
        while (cursor.moveToNext()){
            user = new User(Integer.parseInt(cursor.getString(cursor.getColumnIndexOrThrow("user_id"))),
                    cursor.getString(cursor.getColumnIndexOrThrow("user_name")),cursor.getString(cursor.getColumnIndexOrThrow("user_head_img")),
                    cursor.getString(cursor.getColumnIndexOrThrow("user_phone")),
                    cursor.getString(cursor.getColumnIndexOrThrow("user_password")),
                    cursor.getString(cursor.getColumnIndexOrThrow("user_email")),cursor.getString(cursor.getColumnIndexOrThrow("user_gender")),
                    cursor.getString(cursor.getColumnIndexOrThrow("user_description")));
            user.setHeadImgStr(cursor.getString(cursor.getColumnIndexOrThrow("user_head_img")));
        }
        cursor.close();
        return user;
    }
    public User findUserByEmail(String email){
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.query("user",new String[]{"user_id","user_phone","user_head_img","user_name","user_password","user_email","user_gender","user_description"},"user_email like ?",new String[]{email},null,null,null);
        if (cursor == null) return null;
        User user = null;
        while (cursor.moveToNext()){
            user = new User(Integer.parseInt(cursor.getString(cursor.getColumnIndexOrThrow("user_id"))),
                    cursor.getString(cursor.getColumnIndexOrThrow("user_name")),
                    cursor.getString(cursor.getColumnIndexOrThrow("user_head_img")),
                    cursor.getString(cursor.getColumnIndexOrThrow("user_phone")),
                    cursor.getString(cursor.getColumnIndexOrThrow("user_password")),
                    cursor.getString(cursor.getColumnIndexOrThrow("suer_email")),
                    cursor.getString(cursor.getColumnIndexOrThrow("user_gender")),
                    cursor.getString(cursor.getColumnIndexOrThrow("user_description")));
            user.setHeadImgStr(cursor.getString(cursor.getColumnIndexOrThrow("user_head_img")));
        }
        cursor.close();
        return user;
    }

    // 桌游小类增删改查
    public Boolean addGameCategory(GameCategory gameCategory){
        SQLiteDatabase db = getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("game_category_name",gameCategory.getGameCategoryName());
        values.put("game_category_description",gameCategory.getGameCategoryDescription());
        values.put("game_category_model",gameCategory.getGameCategoryModel().toString());
        values.put("game_category_img",gameCategory.getGameCategoryImg());
        if (db.insert("game_category",null,values) != -1) return Boolean.TRUE;
        return Boolean.FALSE;
    }
    public Boolean updateGameCategory(GameCategory gameCategory){
        SQLiteDatabase db = getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("game_category_name",gameCategory.getGameCategoryName());
        values.put("game_category_description",gameCategory.getGameCategoryDescription());
        values.put("game_category_model",gameCategory.getGameCategoryModel().toString());
        values.put("game_category_img",gameCategory.getGameCategoryImg());
        if (db.update("game_category",values,"game_category_id = ?",new String[]{gameCategory.getGameCategoryId().toString()}) > 0) return Boolean.TRUE;
        return Boolean.FALSE;
    }
    public GameCategory findGameCategoryByName(String name){
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.query("game_category",null,"game_category_name like ?",new String[]{name},null,null,null);
        if (cursor == null) return null;
        GameCategory gameCategory = new GameCategory();
        while(cursor.moveToNext()){
            gameCategory.setGameCategoryId(Integer.parseInt(cursor.getString(cursor.getColumnIndexOrThrow("game_category_id"))));
            gameCategory.setGameCategoryName(cursor.getString(cursor.getColumnIndexOrThrow("game_category_name")));
            gameCategory.setGameCategoryDescription(cursor.getString(cursor.getColumnIndexOrThrow("game_category_description")));
            switch (cursor.getString(cursor.getColumnIndexOrThrow("game_category_model"))){
                case "AMERICAN":
                    gameCategory.setGameCategoryModel(Model.AMERICAN);
                    break;
                case "GERMANY":
                    gameCategory.setGameCategoryModel(Model.GERMANY);
                    break;
                case "TRPG":
                    gameCategory.setGameCategoryModel(Model.TRPG);
                    break;
                case "PARTY":
                    gameCategory.setGameCategoryModel(Model.PARTY);
                    break;
                case "TRADITION":
                    gameCategory.setGameCategoryModel(Model.TRADITION);
                    break;
                case "WAR":
                    gameCategory.setGameCategoryModel(Model.WAR);
                    break;
            }
            gameCategory.setGameCategoryImg(cursor.getString(cursor.getColumnIndexOrThrow("game_category_img")));
        }
        cursor.close();
        return gameCategory;
    }
    public Boolean deleteGameCategoryByName(String name){
        GameCategory gameCategory = findGameCategoryByName(name);
        if (gameCategory == null) return Boolean.FALSE;
        SQLiteDatabase db = getWritableDatabase();
        db.delete("game","game_category_id = ?",new String[]{gameCategory.getGameCategoryId().toString()});
        if (db.delete("game_category","game_category_name like ?",new String[]{name}) > 0) return Boolean.TRUE;
        return Boolean.FALSE;
    }
    public List<GameCategory> findGameCategoryByModel(String model){
        List<GameCategory> gameCategories = new ArrayList<>();
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.query("game_category",null,"game_category_model like ?",new String[]{model},null,null,null);
        if (cursor == null) return null;
        GameCategory gameCategory;
        while(cursor.moveToNext()){
            gameCategory = new GameCategory();
            gameCategory.setGameCategoryId(Integer.parseInt(cursor.getString(cursor.getColumnIndexOrThrow("game_category_id"))));
            gameCategory.setGameCategoryName(cursor.getString(cursor.getColumnIndexOrThrow("game_category_name")));
            gameCategory.setGameCategoryDescription(cursor.getString(cursor.getColumnIndexOrThrow("game_category_description")));
            Model gameModel;
            switch (model){
                case "GERMANY":
                    gameModel=Model.GERMANY;break;
                case "WAR":
                    gameModel=Model.WAR;break;
                case "PARTY":
                    gameModel=Model.PARTY;break;
                case "TRPG":
                    gameModel=Model.TRPG;break;
                case "TRADITION":
                    gameModel=Model.TRADITION;break;
                default:
                    gameModel=Model.AMERICAN;break;
            }
            gameCategory.setGameCategoryModel(gameModel);
            gameCategory.setGameCategoryImg(cursor.getString(cursor.getColumnIndexOrThrow("game_category_img")));
            gameCategories.add(gameCategory);

        }
        cursor.close();
        return gameCategories;
    }
    public GameCategory findGameCategoryById(Integer gameCategoryId){
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.query("game_category",null,"game_category_id = ?",new String[]{gameCategoryId.toString()},null,null,null);
        if (cursor == null) return null;
        GameCategory gameCategory = new GameCategory();
        while(cursor.moveToNext()){
            gameCategory.setGameCategoryId(Integer.parseInt(cursor.getString(cursor.getColumnIndexOrThrow("game_category_id"))));
            gameCategory.setGameCategoryName(cursor.getString(cursor.getColumnIndexOrThrow("game_category_name")));
            gameCategory.setGameCategoryDescription(cursor.getString(cursor.getColumnIndexOrThrow("game_category_description")));
            switch (cursor.getString(cursor.getColumnIndexOrThrow("game_category_model"))){
                case "AMERICAN":
                    gameCategory.setGameCategoryModel(Model.AMERICAN);
                    break;
                case "GERMANY":
                    gameCategory.setGameCategoryModel(Model.GERMANY);
                    break;
                case "TRPG":
                    gameCategory.setGameCategoryModel(Model.TRPG);
                    break;
                case "PARTY":
                    gameCategory.setGameCategoryModel(Model.PARTY);
                    break;
                case "TRADITION":
                    gameCategory.setGameCategoryModel(Model.TRADITION);
                    break;
                case "WAR":
                    gameCategory.setGameCategoryModel(Model.WAR);
                    break;
            }
            gameCategory.setGameCategoryImg(cursor.getString(cursor.getColumnIndexOrThrow("game_category_img")));
        }
        cursor.close();
        return gameCategory;
    }

    // 桌游增删改查
    // TODO
    public Boolean addGame(Game game){
        SQLiteDatabase db = getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("game_description",game.getGameDescription());
        values.put("owner_name",game.getOwnerName());
        values.put("game_category_id",game.getGameCategoryId());
        if (db.insert("game",null,values) != -1) return Boolean.TRUE;
        return Boolean.FALSE;
    }
    public Boolean updateGame(Game game){
        SQLiteDatabase db = getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("game_description",game.getGameDescription());
        if (db.update("game",values,"game_id = ?",new String[]{game.getGameId().toString()}) > 0) return Boolean.TRUE;
        return Boolean.FALSE;
    }
    public Boolean deleteGameById(Integer gameId){
        if (findGameByGameId(gameId) == null) return Boolean.FALSE;
        SQLiteDatabase db = getWritableDatabase();
        if (db.delete("game","game_id = ?",new String[]{gameId.toString()}) > 0) return Boolean.TRUE;
        return Boolean.FALSE;
    }
    public Game findGameByGameId(Integer gameId){
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.query("game",null,"game_id = ?",new String[]{gameId.toString()},null,null,null);
        if (cursor == null) return null;
        Game game = new Game();
        while(cursor.moveToNext()){
            game.setGameId(Integer.parseInt(cursor.getString(cursor.getColumnIndexOrThrow("game_id"))));
            game.setOwnerName(cursor.getString(cursor.getColumnIndexOrThrow("owner_name")));
            game.setGameCategoryId(Integer.parseInt(cursor.getString(cursor.getColumnIndexOrThrow("game_category_id"))));
            game.setGameDescription(cursor.getString(cursor.getColumnIndexOrThrow("game_description")));
        }
        cursor.close();
        return game;
    }
    public ArrayList<Game> findGameByName(String gameName){
        SQLiteDatabase db = getReadableDatabase();
        GameCategory gameCategory = findGameCategoryByName(gameName);
        Cursor cursor = db.query("game",null,"game_category_id = ?",new String[]{gameCategory.getGameCategoryId().toString()},null,null,null);
        if (cursor == null) return null;
        ArrayList<Game> gameList = new ArrayList<>();
        Game game;
        while(cursor.moveToNext()){
            game = new Game();
            game.setGameId(Integer.parseInt(cursor.getString(cursor.getColumnIndexOrThrow("game_id"))));
            game.setOwnerName(cursor.getString(cursor.getColumnIndexOrThrow("owner_name")));
            game.setGameCategoryId(Integer.parseInt(cursor.getString(cursor.getColumnIndexOrThrow("game_category_id"))));
            game.setGameDescription(cursor.getString(cursor.getColumnIndexOrThrow("game_description")));
            gameList.add(game);
        }
        cursor.close();
        return gameList;
    }
    public ArrayList<Game> findPersonalGame(String ownerName){
        SQLiteDatabase db = getReadableDatabase();

        Cursor cursor = db.query("game",null,"owner_name = ?",new String[]{ownerName},null,null,null);
        if (cursor == null) return null;
        ArrayList<Game> gameList = new ArrayList<>();
        Game game;
        while(cursor.moveToNext()){
            game = new Game();
            game.setGameId(Integer.parseInt(cursor.getString(cursor.getColumnIndexOrThrow("game_id"))));
            game.setOwnerName(cursor.getString(cursor.getColumnIndexOrThrow("owner_name")));
            game.setGameCategoryId(Integer.parseInt(cursor.getString(cursor.getColumnIndexOrThrow("game_category_id"))));
            game.setGameDescription(cursor.getString(cursor.getColumnIndexOrThrow("game_description")));
            gameList.add(game);
        }
        cursor.close();
        return gameList;
    }

    // 我的喜欢桌游增删改查
    // TODO 把添加和删除喜欢的类全部修改为 桌游大类而不是game
    public Boolean addLike(String userName,Integer gameId){
        SQLiteDatabase db = getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("user_name",userName);
        values.put("game_id",gameId);
        if (db.insert("ilike",null,values) != -1) return Boolean.TRUE;
        return Boolean.FALSE;
    }
    public Boolean deleteLike(String userName,Integer gameId){
        return Boolean.FALSE;
    }
    public List<Game> findPersonLikeGames(String userName){
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.query("ilike",null,"user_name = ?",new String[]{userName},null,null,null);
        if (cursor == null) return null;
        ArrayList<Game> gameList = new ArrayList<>();

        while(cursor.moveToNext()){
            gameList.add(findGameByGameId(cursor.getInt(cursor.getColumnIndexOrThrow("game_id"))));
        }
        cursor.close();
        return gameList;
    }

    // 帖子增删改查
    public Boolean addPost(Post post){
        SQLiteDatabase db = getWritableDatabase();
        ContentValues values = new ContentValues();
        return Boolean.FALSE;
    }
}
